package com.DosonLeung.homeschoolcommunication.SQLite;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;

/**
 * Created by DosonLeung on 2014/11/18.
 */
public class MembersDB extends SQLiteOpenHelper {
    /**
     * @param context
     * @param name
     * @param version
     */
    public MembersDB(Context context, String name, int version) {
        super(context, name, null, version);
    }

    /*
     name 姓名
     sex 性别(1男 2女)
     description 简介(家长填写孩子姓名、老师填写教的科目)
     role 角色(家长填写监护角色、老师填写职务)
     type 1家长 2老师
     phone 联系方式
     lasttime 最后一次聊天时间
     lastnote 最后一条聊天记录
     lastnotetype 最后一条聊天记录类型
    * */
    @Override
    public void onCreate(SQLiteDatabase DB) {
        try {
            String SQL = "create table members " + " (uid VARCHAR(128) primary key, classid VARCHAR(128) , " +
                    "name VARCHAR(128), sex VARCHAR(2), type VARCHAR(2)," +
                    " description VARCHAR(128), role VARCHAR(128), phone VARCHAR(128), " +
                    "avatar_path VARCHAR(255), lasttime VARCHAR(128), lastnote VARCHAR(128), lastnotetype VARCHAR(128))";
            DB.execSQL(SQL);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("--------onUpdate Called--------"
                + oldVersion + "--->" + newVersion);
    }

    public void insert(Map<String, String> item) {
        try {
            String uid = item.get("uid");
            String classid = item.get("classid");
            String name = item.get("name");
            String sex = item.get("sex");
            String type = item.get("type");
            String description = item.get("description");
            String role = item.get("role");
            String phone = item.get("phone");
            String avatar_path = item.get("avatar_path");
            String lasttime = item.get("lasttime");
            String lastnote = item.get("lastnote");
            String lastnotetype = item.get("lastnotetype");
            this.getReadableDatabase().execSQL("insert into members" + " values(? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)"
                    , new String[]{uid, classid, name, sex, type, description, role, phone, avatar_path, lasttime, lastnote, lastnotetype});
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //从数据库获取出联系人记录（sum : all）
    public LinkedList<Map<String, String>> getMembersData() {
        LinkedList<Map<String, String>> list = new LinkedList<Map<String, String>>();
        try {
            Cursor cursor = this.getReadableDatabase().rawQuery("select * from members order by lasttime", null);
            while (cursor.moveToNext()) {
                //将结果集中的数据存入ArrayList中
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("uid", cursor.getString(0));
                map.put("classid", cursor.getString(1));
                map.put("name", cursor.getString(2));
                map.put("sex", cursor.getString(3));
                map.put("type", cursor.getString(4));
                map.put("description", cursor.getString(5));
                map.put("role", cursor.getString(6));
                map.put("phone", cursor.getString(7));
                map.put("avatar_path", cursor.getString(8));
                map.put("lasttime", cursor.getString(9));
                map.put("lastnote", cursor.getString(10));
                map.put("lastnotetype", cursor.getString(11));
                list.add(map);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //从数据库获取记录（sum :联系人数量）
    public LinkedList<Map<String, String>> getMembersData(int offset, int sum) {
        LinkedList<Map<String, String>> list = new LinkedList<Map<String, String>>();
        String start = (Integer.valueOf(offset)).toString();
        String length = (Integer.valueOf(sum)).toString();
        try {
            Cursor cursor = this.getReadableDatabase().rawQuery("select * from members" + " order by lasttime" +
                    " limit " + start + " offset " + length, null);
            while (cursor.moveToNext()) {
                //将结果集中的数据存入ArrayList中
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("uid", cursor.getString(0));
                map.put("classid", cursor.getString(1));
                map.put("name", cursor.getString(2));
                map.put("sex", cursor.getString(3));
                map.put("type", cursor.getString(4));
                map.put("description", cursor.getString(5));
                map.put("role", cursor.getString(6));
                map.put("phone", cursor.getString(7));
                map.put("avatar_path", cursor.getString(8));
                map.put("lasttime", cursor.getString(9));
                map.put("lastnote", cursor.getString(10));
                map.put("lastnotetype", cursor.getString(11));
                list.add(map);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public HashMap<String,String> getRowByUid(String uid){
        String sql = String.format("select * from members where uid = '%s'",uid);
        HashMap<String, String> map = new HashMap<String, String>();
        try {
            Cursor cursor = this.getReadableDatabase().rawQuery(sql, null);
            while (cursor.moveToNext()) {
                map = new HashMap<String, String>();
                map.put("uid", cursor.getString(0));
                map.put("classid", cursor.getString(1));
                map.put("name", cursor.getString(2));
                map.put("sex", cursor.getString(3));
                map.put("type", cursor.getString(4));
                map.put("description", cursor.getString(5));
                map.put("role", cursor.getString(6));
                map.put("phone", cursor.getString(7));
                map.put("avatar_path", cursor.getString(8));
                map.put("lasttime", cursor.getString(9));
                map.put("lastnote", cursor.getString(10));
                map.put("lastnotetype", cursor.getString(11));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return map;
    }

    public String getElementByUid(String element,String uid){
        String sql = String.format("select %s from members where uid = '%s'",element,uid);
        String elements = "";
        try {
            Cursor cursor = this.getReadableDatabase().rawQuery(sql, null);
            while (cursor.moveToNext()) {
                elements = cursor.getString(0);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return elements;
    }

    //除了uid外 其余不跟新
    public void update(Map<String, String> item) {
        try {
            String uid = item.get("uid");
            String classid = item.get("classid");
            String name = item.get("name");
            String sex = item.get("sex");
            String type = item.get("type");
            String description = item.get("desciption");
            String role = item.get("role");
            String phone = item.get("phone");
            String avatar_path = item.get("avatar_path");
            String lasttime = item.get("lasttime");
            String lastnote = item.get("lastnote");
            String lastnotetype = item.get("lastnotetype");
            this.getReadableDatabase().execSQL("update members" + " set " +
                    "classid = ?, " + "name = ?, " + "sex = ?, " + "type = ?, " +
                    "description = ?, " + "role = ? " + "phone = ?" + "avatar_path = ?" +
                    "lasttime = ?, " + "lastnote = ?, " + "lastnotetype = ? " +
                    "where uid = ?"
                    , new String[]{classid, name, sex, type, description, role, phone, avatar_path,
                    lasttime, lastnote, lastnotetype, uid});
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //因业务需求跟新部分
    public void updateMembersPart(Map<String, String> item){
        try {
            String uid = item.get("uid");
            String lasttime = item.get("lasttime");
            String lastnote = item.get("lastnote");
            String lastnotetype = item.get("lastnotetype");
            String SQL = String.format("update members" + " set " +
                    "lasttime = '%s', " + "lastnote = '%s', " + "lastnotetype = '%s' " +
                    "where uid = '%s'", lasttime, lastnote, lastnotetype, uid);
            this.getReadableDatabase().execSQL(SQL);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //清空联系人记录
    public void cleanTable() {
        try {
            this.getReadableDatabase().execSQL("delete from members");
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //获取联系人数量
    public int getCount() {
        String sum = "0";
        try {
            Cursor cursor = this.getReadableDatabase().rawQuery("select count(uid) from members", null);
            while (cursor.moveToNext()) {
                sum = cursor.getString(0);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return Integer.parseInt(sum);
    }
}

